package com.itheima.service.impl;

import com.itheima.mapper.ReportMapper;
import com.itheima.pojo.*;
import com.itheima.service.ReportService;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ResourceLoader;
import org.springframework.stereotype.Service;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.time.*;
import java.time.format.DateTimeFormatter;
import java.time.temporal.TemporalAdjusters;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.stream.Collectors;

@Service
@Slf4j
public class ReportServiceImpl implements ReportService {
    @Autowired
    private ReportMapper reportMapper;
    /**
     * 获取会员数量统计数据
     */
    @Override
    public MemberNum getMemberReport() {
        //准备返回数据
        MemberNum memberNum = new MemberNum();
        //1.首先拿到表中的Date类型的日期字段的最大值和最小值
        Date maxDate = reportMapper.getMaxDate();
        Date minDate = reportMapper.getMinDate();
        log.info("最大值：{}，最小值：{}",maxDate,minDate);
        //2.根据最大值和最小值，取出包含在内的所有的月份
        if (maxDate == null || minDate == null) {
            log.info("没有会员数据");
            return null;
        }
        List<Date> allMonths = getAllMonthsInRange(minDate, maxDate);
        //3.根据集合月份的每月的最大值和最小值，查询每月的会员数量
        //需要List<Integer> memberCount，来收集每月的会员数量
        List<Integer> memberCount = new ArrayList<>();
        //把每个月的最后一天的日期作为月份封装到MemberNum的List<String> months中
        List<String> months = new ArrayList<>();
        //遍历allMonths拿到该月的最大值和最小值，查询每月的会员数量
        for (Date month : allMonths) {
            Date maxDateOfMonth = getLastDayOfMonth(month);
            Date minDateOfMonth = getFirstDayOfMonth(month);
            Integer count = reportMapper.getMemberCountByMonth(minDateOfMonth, maxDateOfMonth);
            //初步封装数量和日期
            memberCount.add(count);
            //把每个月的最后一天的日期作为月份封装到MemberNum的List<String> months中，日期格式化例如"2020.01.01"
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy.MM.dd");
            String formattedDate = sdf.format(maxDateOfMonth);
            months.add(formattedDate);
        }
        //最后封装
        memberNum.setMonths(months);
        memberNum.setMemberCount(memberCount);
        return memberNum;
    }
    /**
     * 套餐占比统计
     */
    @Override
    public SetMealReport getSetmealReport() {
        SetMealReport setMealReport = new SetMealReport();
        //从表中拿到所有的name，为了保险起见注意去重
        List<String> setmealNames =  reportMapper.getmealNames();
        setmealNames = setmealNames.stream()
                .distinct()
                .collect(Collectors.toList());
        //直接封装到返回值中
        setMealReport.setSetmealNames(setmealNames);
        //查询setmeal表和setmeal_checkgroup表
        List<SetMealReport2> setmealCounts = new ArrayList<>();
        //遍历setmealNames
        for (String setmealName : setmealNames) {
            //用name去获得对应的id，再从setmeal_checkgroup表中查这个setmeal_id数量
            Integer count = reportMapper.getSetmealIdByName(setmealName);
            //直接封装进setmealCounts中
            setmealCounts.add(new SetMealReport2(setmealName,count));
        }
        //封装到返回值中
        setMealReport.setSetmealCount(setmealCounts);
        return setMealReport;
    }
    /**
     * 获取运营统计数据
     */
    @Override
    public BusinessReportData getBusinessReportData() {
        //准备最终返回数据
        BusinessReportData businessReportData = new BusinessReportData();
        /**
         * 准备当前系统时间
         */
        //获取当前系统日期
        LocalDate localDate = LocalDate.now();
        //将 LocalDate 转换为 Date
        Date date = Date.from(localDate.atStartOfDay(ZoneId.systemDefault()).toInstant());
        //封装进最终结果
        businessReportData.setReportDate(date);
        /**
         * 准备今日新增会员数
         */
        // 得到今天的日期最小值和最大值
        // 获取今天 0 点的 LocalDateTime 对象
        LocalDateTime startOfDay = localDate.atStartOfDay();
        // 将 LocalDateTime 转换为 Date
        Date startDate = Date.from(startOfDay.atZone(ZoneId.systemDefault()).toInstant());
        // 获取今天 23:59:59 的 LocalDateTime 对象
        LocalDateTime endOfDay = localDate.atTime(LocalTime.MAX);
        // 将 LocalDateTime 转换为 Date
        Date endDate = Date.from(endOfDay.atZone(ZoneId.systemDefault()).toInstant());
        //查询
        Integer todayNewMember = reportMapper.getMemberCountByDay(startDate, endDate);
        /**
         * 准备本月新增会员数
         */
        // 获取本月第一天
        LocalDate firstDay = localDate.withDayOfMonth(1);
        // 设置时间为 0 点
        LocalDateTime startOfDay1 = firstDay.atStartOfDay();
        // 将 LocalDateTime 转换为 Date
        Date startOfDay2 = Date.from(startOfDay1.atZone(ZoneId.systemDefault()).toInstant());
        // 获取本月最后一天
        LocalDate lastOfDay = localDate.withDayOfMonth(localDate.lengthOfMonth());
        // 设置时间为 23:59:59
        LocalDateTime lastOfDay1 = lastOfDay.atTime(LocalTime.MAX);
        // 将 LocalDateTime 转换为 Date
        Date lastOfDay2 = Date.from(endOfDay.atZone(ZoneId.systemDefault()).toInstant());
        //查询
        Integer thisMonthNewMember = reportMapper.getMemberCountByDay(startOfDay2, lastOfDay2);
        //直接封装进最终结果
        businessReportData.setThisMonthNewMember(todayNewMember);
        /**
         * 本周新会员人数
         */
        // 获取本周第一天（周一）
        LocalDate firstDay1 = localDate.with(TemporalAdjusters.previousOrSame(DayOfWeek.MONDAY));
        // 设置时间为 0 点
        LocalDateTime firstDay2 = firstDay.atStartOfDay();
        // 将 LocalDateTime 转换为 Date
        Date firstDay3 = Date.from(startOfDay.atZone(ZoneId.systemDefault()).toInstant());
        // 获取本周最后一天（周日）
        LocalDate lastDay1 = localDate.with(TemporalAdjusters.nextOrSame(DayOfWeek.SUNDAY));
        // 设置时间为 23:59:59
        LocalDateTime lastDay2 = lastDay1.atTime(LocalTime.MAX);
        // 将 LocalDateTime 转换为 Date
        Date lastDay3 = Date.from(endOfDay.atZone(ZoneId.systemDefault()).toInstant());
        //查询
        Integer thisWeekNewMember = reportMapper.getMemberCountByWeek(firstDay3, lastDay3);
        //直接封装进最终结果
        businessReportData.setThisWeekNewMember(thisWeekNewMember);
        /**
         * 会员总数
         */
        businessReportData.setTotalMember(reportMapper.getTotalMember());
        /**
         * 今日订单数量
         */
        businessReportData.setTodayOrderNumber(reportMapper.getOrderNumber(startDate, endDate));
        /**
         * 本周订单数量
         */
        businessReportData.setThisWeekOrderNumber(reportMapper.getOrderNumber(firstDay3, lastDay3));
        /**
         * 本月订单数量
         */
        businessReportData.setThisMonthOrderNumber(reportMapper.getOrderNumber(startOfDay2, lastOfDay2));
        /**
         * 热门套餐
         */
        //从表中拿到所有的name，为了保险起见注意去重
        List<String> setmealNames =  reportMapper.getmealNames();
        setmealNames = setmealNames.stream()
                .distinct()
                .collect(Collectors.toList());
        //第一次遍历setmealNames求订单总量
        Integer sum = 0;
        for (int i = 0; i < setmealNames.size(); i++) {
            //用name去获得对应的id，再从setmeal_checkgroup表中查这个setmeal_id数量,求总量
            sum += reportMapper.getSetmealIdByName(setmealNames.get(i));
        }
        //第二次遍历setmealNames
        List<BusinessReportData2> hotSetmeal = new ArrayList<>();
        for (Integer i = 0, count; i < setmealNames.size(); i++) {
            //用name去获得对应的id，再从setmeal_checkgroup表中查这个setmeal_id数量
            count = reportMapper.getSetmealIdByName(setmealNames.get(i));
            //封装,注意sum不能为0
            if(sum == 0){
                log.info("除数为0，不予计算");
                return null;
            }
            hotSetmeal.add(new BusinessReportData2((float) count/sum,setmealNames.get(i),count));
        }
        //直接封装进最终结果
        businessReportData.setHotSetmeal(hotSetmeal);
        /**
         * 本日到诊人数
         */
        businessReportData.setTodayVisitsNumber(reportMapper.getVisitsNumber(startDate, endDate));
        /**
         * 本周到诊人数
         */
        businessReportData.setThisWeekVisitsNumber(reportMapper.getVisitsNumber(firstDay3, lastDay3));
        /**
         * 本月到诊人数
         */
        businessReportData.setThisMonthVisitsNumber(reportMapper.getVisitsNumber(startOfDay2, lastOfDay2));
        /**
         * 返回最终结果
         */
        return businessReportData;
    }
    /**
     * 导出运营数据到Excel并提供客户端下载
     */
    @Autowired
    private ResourceLoader resourceLoader;
    @Override
    public void exportBusinessReport(HttpServletResponse httpServletResponse) {
        //调用统计报表服务方法拿到数据
        BusinessReportData businessReportData = getBusinessReportData();
        //基于模版文件创建POI输入流
        try {
            InputStream inputStream = resourceLoader.getResource("classpath:templates/report_template.xlsx").getInputStream();
            XSSFWorkbook excel = new XSSFWorkbook(inputStream);
            //填充数据--获得标签页
            XSSFSheet sheet = excel.getSheet("Sheet1");

            // 定义日期格式
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
            // 将 Date 转换为 LocalDate
            LocalDate localDate = businessReportData.getReportDate().toInstant().atZone(ZoneId.systemDefault()).toLocalDate();

            //自定义方法填充表格
            setCellValue(sheet,2,5,localDate.format(formatter));

            setCellValue(sheet,4,5,businessReportData.getTodayNewMember());
            setCellValue(sheet,4,7,businessReportData.getTotalMember());

            setCellValue(sheet,5,5,businessReportData.getThisWeekNewMember());
            setCellValue(sheet,5,7,businessReportData.getThisMonthNewMember());

            setCellValue(sheet,7,5,businessReportData.getTodayOrderNumber());
            setCellValue(sheet,8,5,businessReportData.getThisWeekOrderNumber());
            setCellValue(sheet,9,5,businessReportData.getThisMonthOrderNumber());

            setCellValue(sheet,7,7,businessReportData.getTodayVisitsNumber());
            setCellValue(sheet,8,7,businessReportData.getThisWeekVisitsNumber());
            setCellValue(sheet,9,7,businessReportData.getThisMonthVisitsNumber());

            //从第12行第四格开始写入List数据
            for (int i = 0,row = 12; i < businessReportData.getHotSetmeal().size(); i++,row++) {
                for (int column = 4; column < 7; column++) {
                    if(column == 4)setCellValue(sheet,row,column,businessReportData.getHotSetmeal().get(i).getName());
                    if(column == 5)setCellValue(sheet,row,column,businessReportData.getHotSetmeal().get(i).getValue());
                    if(column == 6)setCellValue(sheet,row,column,businessReportData.getHotSetmeal().get(i).getProportion());
                }
            }
            //通过输出流把文件下载到客户端
            ServletOutputStream outputStream = httpServletResponse.getOutputStream();
            excel.write(outputStream);
            //关闭流
            outputStream.close();
            excel.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    /**
     * 操作指定的某个单元格
     * @param sheet 操作的标签页
     * @param row 第几行，从0开始
     * @param column 第几列，从0开始
     * @param value 要书写的值
     */
    private void setCellValue(XSSFSheet sheet,int row, int column, Object value) {
        //获取行对象
        XSSFRow row0 = sheet.createRow(row);
        //操作列，记得判空
        if(row0.getCell(column) == null){
            row0.createCell(column);
        }
        //注意value只能是基本数据类型包装类，否则需要手动转换字符串
        row0.getCell(column).setCellValue(value.toString());
    }




    /**
     * 根据最大值和最小值，取出包含在内的所有的月份
     * @param minDate
     * @param maxDate
     * @return
     */
    private static List<Date> getAllMonthsInRange(Date minDate, Date maxDate) {
        // 将 Date 转换为 LocalDate
        LocalDate minLocalDate = minDate.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();
        LocalDate maxLocalDate = maxDate.toInstant().atZone(ZoneId.systemDefault()).toLocalDate();

        List<Date> dates = new ArrayList<>();
        YearMonth currentMonth = YearMonth.from(minLocalDate);
        YearMonth endMonth = YearMonth.from(maxLocalDate);

        // 遍历区间内的所有月份
        while (!currentMonth.isAfter(endMonth)) {
            // 获取该月的第一天
            LocalDate firstDayOfMonth = currentMonth.atDay(1);
            // 将 LocalDate 转换为 Date
            Date date = Date.from(firstDayOfMonth.atStartOfDay(ZoneId.systemDefault()).toInstant());
            dates.add(date);
            currentMonth = currentMonth.plusMonths(1);
        }
        return dates;
    }
    /**
     * 获取指定日期所在月的第一天
     * @param date 指定日期
     * @return 该月第一天的 Date 对象
     */
    private static Date getFirstDayOfMonth(Date date) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        // 设置为该月第一天
        calendar.set(Calendar.DAY_OF_MONTH, 1);
        // 把时、分、秒、毫秒设置为 0
        calendar.set(Calendar.HOUR_OF_DAY, 0);
        calendar.set(Calendar.MINUTE, 0);
        calendar.set(Calendar.SECOND, 0);
        calendar.set(Calendar.MILLISECOND, 0);
        return calendar.getTime();
    }

    /**
     * 获取指定日期所在月的最后一天
     * @param date 指定日期
     * @return 该月最后一天的 Date 对象
     */
    private static Date getLastDayOfMonth(Date date) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTime(date);
        // 设置为该月最后一天
        calendar.set(Calendar.DAY_OF_MONTH, calendar.getActualMaximum(Calendar.DAY_OF_MONTH));
        // 把时、分、秒、毫秒设置为该天的最后一刻
        calendar.set(Calendar.HOUR_OF_DAY, 23);
        calendar.set(Calendar.MINUTE, 59);
        calendar.set(Calendar.SECOND, 59);
        calendar.set(Calendar.MILLISECOND, 999);
        return calendar.getTime();
    }
}
